package org.flexqueries2.core
{
	import flash.events.Event;
	import flash.events.EventDispatcher;
	import flash.utils.flash_proxy;
	
	import mx.messaging.messages.HTTPRequestMessage;
	import mx.messaging.messages.IMessage;
	import mx.rpc.events.FaultEvent;
	import mx.rpc.events.ResultEvent;
	import mx.rpc.http.HTTPService;
	
	import org.flexqueries2.components.messagebox.Message;
	import org.flexqueries2.core.events.SqlEvent;
	import org.flexqueries2.utils.crypto.Base64;
	import org.flexqueries2.utils.date.DateUtils;
	import org.flexqueries2.utils.logging.Logger;
	
	/**
	 * Evenément lancé quand la réponse de la requête SQL (select) a été reçue par le programme
	 */ 
	[Event(name="selectComplete", type="org.flexqueries2.core.events.SqlEvent")]
	
	/**
	 * Evenément lancé quand la réponse de la requête SQL (non select) a été reçue par le programme
	 */ 
	[Event(name="executeComplete", type="org.flexqueries2.core.events.SqlEvent")]
	
	/**
	 * Evenément lancé quand on passe à l'enregistrement suivant par la methode read()
	 */ 
	[Event(name="sqlRead", type="org.flexqueries2.core.events.SqlEvent")]
	
	/**
	 * Evenément lancé en cas d'erreur SQL
	 */ 
	[Event(name="sqlError", type="org.flexqueries2.core.events.SqlEvent")]
	public class SqlQuery extends EventDispatcher
	{
		/**
		 * Obligatoire! Définit l'adresse du service PHP pour l'application entière
		 */ 
		public static var serviceUrl:String = 'http://localhost/flexqueries-2.0/svn/php/flexqueries2/www/fc.php';
		
		/**
		 * Encodage des paramètres POST au format base64. Default : true
		 */ 
		//public static var base64Encoding:Boolean = true;
		
		/**
		 * Texte de la requete SQL
		 */ 
		[Bindable]
		public var sql:String;
		
		/**
		 * Tableau des paramètres SQL
		 */ 
		[Bindable]
		public var sqlParams:Array;
		
		/**
		 * Indique si on est en attente d'un résultat 
		 */
		[Bindable]
		public var isPending:Boolean = false;
		
		/**
		 * True pour lire la 1ere ligne automatiquement 
		 */
		[Bindable]
		public var autoReadFirstRow:Boolean = false;
		
		[Bindable]
		private var _xmlAnswer:XML;
		
		private var _currentRowIndex:Number = -1;
		
		private var _affectedRows:Number = 0;
		
		[Bindable(event='affectedRowsChanged')]
		public function get affectedRows():Number
		{
			return _affectedRows;
		}
		

		public function SqlQuery()
		{
		}
		
		/**
		 * Lance un select asynchrone
		 * @param sql:String : Commande SQL à exécuter
		 * @param sqlParams:Array : Tableau des paramètres de la requete
		 */ 
		public function selectAsync(sql:String = null, sqlParams:Array = null):void
		{
			if(sql == null)
				sql = this.sql;
			if(sqlParams == null)
				sqlParams = this.sqlParams;
			
			if(sqlParams == null)
				sqlParams = [];
			var http:HTTPService = getNewHTTPService();
			http.addEventListener(ResultEvent.RESULT, onSelectResult);
			http.addEventListener(FaultEvent.FAULT, onFault);
			
			_currentRowIndex = -1;
			_xmlAnswer = null;
			isPending = true;
			dispatchEvent(new SqlEvent(SqlEvent.SQL_RESET));
			
			Logger.info("selectAsync : " + sql);
			
			if(sql == null)
				throw new Error("No sql defined");
			
			http.send({action: 'select', sql : Base64.encode(sql), sqlParams: encodeParams(sqlParams).toXMLString()});
		}
		
		/**
		 * Lance un select synchrone
		 * @param sql:String : Commande SQL à exécuter
		 * @param sqlParams:Array : Tableau des paramètres de la requete
		 */ 
		public function selectSync(sql:String = null, sqlParams:Array = null):void
		{
			if(sql == null)
				sql = this.sql;
			if(sqlParams == null)
				sqlParams = this.sqlParams;
			
			if(sqlParams == null)
				sqlParams = [];
						
			_currentRowIndex = -1;
			_xmlAnswer = null;
			isPending = true;
			dispatchEvent(new SqlEvent(SqlEvent.SQL_RESET));
			
			Logger.info("selectSync : " + sql);
			
			var result:String = SynchronousHttp.send(SqlQuery.serviceUrl, {action: 'select', sql : Base64.encode(sql), sqlParams: encodeParams(sqlParams).toXMLString()});
			
			//post process:
			var ev:ResultEvent = new ResultEvent(ResultEvent.RESULT, false, true, result);
			onSelectResult(ev);
		}
		
		
		/**
		 * Lance une commande SQL asynchrone (update, delete, insert)
		 * @param sql:String : Commande SQL à exécuter
		 * @param sqlParams:Array : Tableau des paramètres de la requete
		 */ 
		public function execAsync(sql:String = null, sqlParams:Array = null):void
		{
			if(sql == null)
				sql = this.sql;
			if(sqlParams == null)
				sqlParams = this.sqlParams;
			
			if(sqlParams == null)
				sqlParams = [];
			var http:HTTPService = getNewHTTPService();
			http.addEventListener(ResultEvent.RESULT, onExecuteResult);
			http.addEventListener(FaultEvent.FAULT, onFault);
			
			_affectedRows = 0;
			_currentRowIndex = -1;
			isPending = true;
			
			Logger.info(sql);
			
			http.send({action: 'execute', sql : Base64.encode(sql), sqlParams: encodeParams(sqlParams)});
		}
		
		/**
		 * Lance une commande SQL synchrone (update, delete, insert)
		 * @param sql:String : Commande SQL à exécuter
		 * @param sqlParams:Array : Tableau des paramètres de la requete
		 */ 
		public function execSync(sql:String = null, sqlParams:Array = null):void
		{
			if(sql == null)
				sql = this.sql;
			if(sqlParams == null)
				sqlParams = this.sqlParams;
			
			if(sqlParams == null)
				sqlParams = [];
			
			_affectedRows = 0;
			_currentRowIndex = -1;
			isPending = true;
			
			var result:String = SynchronousHttp.send(SqlQuery.serviceUrl, {action: 'execute', sql : Base64.encode(sql), sqlParams: encodeParams(sqlParams)});
			//post process:
			var ev:ResultEvent = new ResultEvent(ResultEvent.RESULT, false, true, result);
			onExecuteResult(ev);
		}
		
		public static function getNewHTTPService():HTTPService
		{
			var http:HTTPService = new HTTPService();
			http.url = serviceUrl;
			http.method = 'POST';
			http.resultFormat = 'e4x';
			http.showBusyCursor = true;
			
			return http;
		}
		
		private function encodeParams(prm:Array):XML
		{
			var x:XML = <params/>;
			var p:String;
			for(var i:Number=0;i<prm.length;i++)
			{
				if(prm[i] == null)
					prm[i] = '';
				
				if(prm[i] is Date)
					prm[i] = DateUtils.formatDate(prm[i]);
				
				p = Base64.encode(prm[i].toString());
				
				x.appendChild(<param>{p}</param>);
			}
			return x;
		}
		
		private function onSelectResult(e:ResultEvent):void
		{
			Logger.info("rows received");
			this._xmlAnswer = XML(e.result);
			this.decodeBase64Xml(this._xmlAnswer);
			Logger.info(this._xmlAnswer.value.table.rows.row.length() + " rows");
			
			dispatchEvent(new SqlEvent(SqlEvent.SELECT_COMPLETE));
			
			isPending = false;
			
			if(autoReadFirstRow)
				read();
			
			if(Number(this._xmlAnswer.errorCode) != 0)
			{
				Message.showStatic(Base64.decode(this._xmlAnswer.value.toString()));
			}
		}

		private function onExecuteResult(e:ResultEvent):void
		{
			this._xmlAnswer = XML(e.result);
			this.decodeBase64Xml(this._xmlAnswer);
			this._xmlAnswer.value = Base64.decode(this._xmlAnswer.value.toString());
			if(Number(this._xmlAnswer.errorCode) != 0)
			{
				_affectedRows = 0;
				Message.showStatic(this._xmlAnswer.value.toString());
			}
			else
			{
				_affectedRows = Number(this._xmlAnswer.value);
				dispatchEvent(new Event('affectedRowsChanged'));
			}
			
			dispatchEvent(new SqlEvent(SqlEvent.EXECUTE_COMPLETE));
			
			isPending = false;
		}
		
		private function onFault(e:FaultEvent):void
		{
			Logger.error("SQL Error ; " + e.fault.faultString);
			dispatchEvent(new SqlEvent(SqlEvent.SQL_ERROR));
		}
		
		/**
		 * Lit la ligne suivante
		 * @param backwards:Boolean lit vers l'arrière
		 */ 
		public function read(backwards:Boolean = false):Boolean
		{
			if(rowCount < 0)
				return false;
			
			if(!backwards) //lecture vers l'avant
			{
				if(_currentRowIndex == (rowCount - 1))
					return false;
				
				_currentRowIndex++;
				dispatchEvent(new SqlEvent(SqlEvent.SQL_READ));
				return true;
			}
			else //lecture vers l'arrière
			{
				if(_currentRowIndex <= 0)
					return false;
				
				_currentRowIndex--;
				dispatchEvent(new SqlEvent(SqlEvent.SQL_READ));
				return true;
			}
		}
		
		[Bindable(event="selectComplete")]
		/**
		 * Nombre de lignes retournées par la requête Select
		 */ 
		public function get rowCount():Number
		{
			try
			{
				return _xmlAnswer.value.table.rows.row.length();
			}
			catch(e:Error)
			{}
			return -1;
		}
		
		[Bindable(event="sqlRead")]
		/**
		 * Nombre de lignes retournées par la requête Select
		 */ 
		public function get currentRowIndex():Number
		{
			try
			{
				return _currentRowIndex;
			}
			catch(e:Error)
			{}
			return -1;
		}
		
		[Bindable(event="sqlRead")]
		/**
		 * Fournit la ligne actuellement lue
		 */ 
		public function get currentRow():SqlRow
		{
			if(_currentRowIndex == -1)
				return null;
			
			return new SqlRow(_xmlAnswer.value.table.rows.row[_currentRowIndex]);
		}
		
		[Bindable(event="selectComplete")]
		public function get rawAnswer():XML
		{
			return this._xmlAnswer;
		}
		
		/**
		 * Retourne un champ de currentRow
		 */ 
		public function fieldByName(fieldName:String, forceRowIndex:Number = -1):SqlField
		{
			if(forceRowIndex > -1)
				return new SqlRow(_xmlAnswer.value.table.rows.row[forceRowIndex]).getField(fieldName);
			else
				return currentRow.getField(fieldName);
		}
		
		private function decodeBase64Xml(source:XML):void
		{
			var cols:Array = this.columns;
			source.errorCode = Base64.decode(source.errorCode);
			
			var colXL:XMLList = null;//source.value.rows..col
			for each(var col:String in cols)
			{
				colXL = (source.value.table.rows as XMLList).descendants(col);
				for each(var field:XML in colXL)
				{
					field.setChildren(Base64.decode(field.toString()));
				}
				
			}
			
			/*
			for(var i:Number=0;i<rowCount;i++)
			{
				for each(var col:String in cols)
					fieldByName(col, i).setValue(Base64.decode(fieldByName(col, i).asString));
			}*/
		}
		
		/**
		 * Nombre de colonnes retournées
		 */ 
		public function get columnCount():Number
		{
			try
			{
				return _xmlAnswer.value.table.columns.column.length();
			}
			catch(e:Error)
			{}
			return -1;
		}
		
		/**
		 * Donne le nom de la colonne à un indice donné
		 */ 
		public function getColumnNameAt(index:Number):String
		{
			try
			{
				return _xmlAnswer.value.table.columns.column[index].@name.toString();
			}
			catch(e:Error){}
			return "";
		}
		
		/**
		 * Tableau des colonnes retournées par la requete
		 */ 
		public function get columns():Array
		{
			var arr:Array = [];
			for(var i:Number = 0;i<columnCount;i++)
			{
				arr.push(getColumnNameAt(i));
			}
			return arr;
		}
		
		public static function getId(table:String, field:String):Number
		{
			var result:String = SynchronousHttp.send(SqlQuery.serviceUrl, {action: 'getId', table: table, field: field});
			return Number(result);
		}
		
		public static function getIdAsync(table:String, field:String, callback:Function):void
		{
			var http:HTTPService = getNewHTTPService();
			http.addEventListener(ResultEvent.RESULT, callback);
			http.send({action: 'getId', table: table, field: field});
		}
		
		public function get asXML():XML
		{
			var x:XML = <sqlquery/>;
			
			x.appendChild(<sql>{Base64.encode(this.sql)}</sql>);
			x.appendChild(encodeParams(this.sqlParams));
			
			return x;
		}
		
		public function rewind():void
		{
			this._currentRowIndex = -1;
		}
	}
}